how to join and map using prisma client
My case
AcademyCoach와 User 사이의 릴레이션을 사용하되, userModel
이라는 기본으로 지정된 필드가 아닌 userInfo
라는 임의의 필드에 User 엔티티를 매핑하기 위해 사용한다.
async listAcademyCoach(
academyId: string,
): Promise<AcademyCoachDtoResponse[]> {
return await this.prisma.academyCoach
.findMany({
include: {
userModel: true, // JOIN and select related user entity into userModel
},
where: {
academy: academyId,
},
})
.then((dtos) =>
dtos.map((dto): AcademyCoachDtoResponse => {
const { userModel, ...rest } = dto;
return {
...rest,
userInfo: UserDto.from(userModel), // ← 바로 여기에서 매핑이 이루어진다.
};
}),
);
}
아래는 Prisma Schema
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
name String
createdTime DateTime @default(now()) @db.Date
updatedTime DateTime @updatedAt @db.Date
deletedTime DateTime? @db.Date
AcademyCoach AcademyCoach[]
tickets Ticket[]
@@map("usermodels")
}
model AcademyCoach {
id String @id @default(auto()) @map("_id") @db.ObjectId
academy String @db.ObjectId
user String @db.ObjectId
createdTime DateTime @default(now()) @db.Date
updatedTime DateTime @updatedAt @db.Date
deletedTime DateTime? @db.Date
userModel User @relation(fields: [user], references: [id])
academyModel Academy @relation(fields: [academy], references: [id])
@@map("academycoachmodels")
}
GPT 설전
leftJoinAndMapOne
can achieve my case.Yes, Prisma does not have a direct equivalent to TypeORM's leftJoinAndMapOne
, but you can achieve similar functionality using relations and computed fields.
Solution 1: Using include
or select
If the relation is defined in your schema, you can use include
or select
to fetch the related entity.
Example:
model User {
id Int @id @default(autoincrement())
name String
profile Profile? @relation(fields: [profileId], references: [id])
profileId Int?
}
model Profile {
id Int @id @default(autoincrement())
bio String
user User?
}
To fetch User
with the Profile
included:
const users = await prisma.user.findMany({
include: {
profile: true,
},
});
Solution 2: Using computed
properties via a Service Layer
Since Prisma doesn't allow mapping a joined property directly like TypeORM's leftJoinAndMapOne
, you can manually add the joined property in your service layer.
const usersWithMappedProfile = await prisma.user.findMany({
include: {
profile: true,
},
});
const result = usersWithMappedProfile.map(user => ({
...user,
profileBio: user.profile?.bio || null,
}));
This effectively adds a profileBio
field like leftJoinAndMapOne
does in TypeORM.
Solution 3: Raw SQL with prisma.$queryRaw
If you need a more complex join:
const usersWithProfile = await prisma.$queryRaw`
SELECT u.id, u.name, p.bio AS profileBio
FROM User u
LEFT JOIN Profile p ON u.profileId = p.id;
`;
But this approach does not return a typed model like Prisma usually does.
Conclusion
While Prisma does not have an exact equivalent of leftJoinAndMapOne
, the combination of:
include
/select
for relations- Transforming the result in your service layer
- Using raw SQL queries when necessary
achieves the same effect. The most idiomatic Prisma approach is to use include
and process the mapping at the service layer.